﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class SLA : Basic, ISLA
    {
        private const string sqlGetSiteAndSLA = "SELECT alll.status,alll.title,s2.ID,s2.name FROM (SELECT s.slastatus status,s.slatitle title,i.siteid FROM sla s " +
            " INNER JOIN incident i ON i.request_id=s.applicationid UNION ALL " +
            " SELECT s.slastatus status,s.slatitle title,i.siteid FROM sla s INNER JOIN problem i ON i.request_id=s.applicationid UNION ALL " +
            " SELECT s.slastatus status,s.slatitle title,i.siteid FROM sla s INNER JOIN change i ON i.request_id=s.applicationid) alll " +
            " INNER JOIN sitebranch s2 ON s2.ID = alll.siteid INNER JOIN usersite u ON u.siteid = s2.ID WHERE u.userid=:userID";
        private const string sqlGetSiteAndSLA_BySystemname = " SELECT alll.status,alll.title,s2.ID,s2.alias,alll.type FROM ( " +
            " SELECT s.slastatus status,s.slatitle title,i.siteid,i.businesssystemid,'事件' as type FROM sla s INNER JOIN incident i ON i.request_id=s.applicationid  where i.businesssystemid in (select ID from BusinessSystem bs where bs.Name = :SystemName1) UNION ALL " +
            " SELECT s.slastatus status,s.slatitle title,i.siteid,i.businesssystemid,'事件' as type FROM sla s INNER JOIN problem i ON i.request_id=s.applicationid  where i.businesssystemid in (select ID from BusinessSystem bs where bs.Name = :SystemName2) UNION ALL " +
            " SELECT s.slastatus status,s.slatitle title,i.siteid,i.businesssystemid,'事件' as type FROM sla s INNER JOIN change i ON i.request_id=s.applicationid where i.businesssystemid in (select ID from BusinessSystem bs where bs.Name = :SystemName3) " +
            " ) alll INNER JOIN sitebranch s2 ON s2.ID = alll.siteid INNER JOIN usersite u ON u.siteid = s2.ID WHERE u.userid=:userID ";
        public decimal _userID;

        //HPD:Help Desk响应/解决率  
        //需要传入slaCategorychar,priority (slastatys=1,4,5,7)
        public List<IncidentAndChangeModel> SLAHelpDesk(int? site, int? bnid, DateTime? sTime, DateTime? eTime, string slaCategorychar1, string slaCategorychar2, int priority)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,s.slastatus ID from sla s inner join incident i on s.applicationid = i.request_id where s.applicationtype = 'HPD:Help Desk' and (s.slastatus = 1 or s.slastatus = 4 or s.slastatus = 5 or s.slastatus = 7) and i.Priority = :priority";
            cmdParms.Add(new OracleParameter("priority", OracleDbType.Int32, priority, ParameterDirection.Input));
            if (slaCategorychar1 != null && slaCategorychar2 != null)
            {
                sqlStr += " and (s.SLACATEGORYCHAR =:slaCategorychar1 or s.SLACATEGORYCHAR =:slaCategorychar2)";
                cmdParms.Add(new OracleParameter("slaCategorychar1", OracleDbType.Varchar2, slaCategorychar1, ParameterDirection.Input));
                cmdParms.Add(new OracleParameter("slaCategorychar2", OracleDbType.Varchar2, slaCategorychar2, ParameterDirection.Input));
            }
            if (site != null)
            {
                sqlStr += " and i.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s.slastatus";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //HPD:Help Desk工单详细信息
        //slaCategorychar,priority,slaStatus
        public List<SLAInfoModel> SLAInfoToHelpDesk(int? site, int? bnid, DateTime? sTime, DateTime? eTime, string slaCategorychar1, string slaCategorychar2, int priority, int slaStatus)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select i.request_id ID,s.slatitle Summary,i.priority Priority,i.status Status from incident i inner join sla s on i.request_id = s.applicationid where 1=1";
            sqlStr += " and i.priority =:priority";
            cmdParms.Add(new OracleParameter("priority", OracleDbType.Int32, priority, ParameterDirection.Input));
            //slaStatus判断
            //(slaStatus=1 满足 Inprocess/Met(1,4),2 不满足Missed/Missed Coal(5,7),3警告Warning(9),4等待Pending(2))
            if (slaStatus == 1)
            {
                sqlStr += " and (s.slaStatus =1 or s.slaStatus =4)";
            }
            if (slaStatus == 2)
            {
                sqlStr += " and (s.slaStatus =5 or s.slaStatus =7)";
            }
            if (slaStatus == 3)
            {
                sqlStr += " and s.slaStatus =9";
            }
            if (slaStatus == 4)
            {
                sqlStr += " and s.slaStatus =2";
            }
            if (slaCategorychar1 != null && slaCategorychar2 != null)
            {
                sqlStr += " and (s.SLACATEGORYCHAR =:slaCategorychar1 or s.SLACATEGORYCHAR =:slaCategorychar2)";
                cmdParms.Add(new OracleParameter("slaCategorychar1", OracleDbType.Varchar2, slaCategorychar1, ParameterDirection.Input));
                cmdParms.Add(new OracleParameter("slaCategorychar2", OracleDbType.Varchar2, slaCategorychar2, ParameterDirection.Input));
            }
            if (site != null)
            {
                sqlStr += " and i.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SLAInfoModel>.GetModel_By_OracleDataReader(dr);
        }

        //totest
        //Incident供应商情况
        public List<SiteBranchAndWorkListModel> SLAIncidentToFirm(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select t1.alls,t1.sign,t2.sign2,t3.sign3 from";
            sqlStr += "(select Count(*) sign,i.firm alls from Incident i inner join sla s on i.REQUEST_ID = s.applicationid where s.applicationtype = 'HPD:Help Desk' and i.firm is not null and (s.SLACATEGORYCHAR = '请求解决时间' Or s.SLACATEGORYCHAR = '请求响应时间')";
            if (site != null)
            {
                sqlStr += " and i.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by i.firm) t1 left join";
            sqlStr += "(select Count(*) sign2,i.firm alls from Incident i inner join sla s on i.REQUEST_ID = s.applicationid where s.applicationtype = 'HPD:Help Desk' and i.firm is not null and i.FirmProStatus = 1 and (s.SLACATEGORYCHAR = '请求解决时间' Or s.SLACATEGORYCHAR = '请求响应时间')";
            if (site != null)
            {
                sqlStr += " and i.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by i.firm) t2 on t1.alls = t2.alls left join";
            sqlStr += "(select Count(*) sign3,i.firm alls from Incident i inner join sla s on i.REQUEST_ID = s.applicationid where s.applicationtype = 'HPD:Help Desk' and i.firm is not null and i.FirmProStatus = 2 and (s.SLACATEGORYCHAR = '请求解决时间' Or s.SLACATEGORYCHAR = '请求响应时间')";
            if (site != null)
            {
                sqlStr += " and i.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by i.firm) t3 on t1.alls = t3.alls";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchAndWorkListModel>.GetModel_By_OracleDataReader(dr);
        }

        //totest
        //CHG:Infrastructure Change响应/解决率  
        //需要传入slaCategorychar,impact
        public List<IncidentAndChangeModel> SLAChange(int? site, int? bnid, DateTime? sTime, DateTime? eTime, string slaCategorychar1, string slaCategorychar2, int priority)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,s.slastatus ID from sla s inner join Change c on s.applicationid = c.request_id where s.applicationtype = 'CHG:Infrastructure Change' and c.priority = :priority";
            cmdParms.Add(new OracleParameter("priority", OracleDbType.Int32, priority, ParameterDirection.Input));
            if (slaCategorychar1 != null && slaCategorychar2 != null)
            {
                sqlStr += " and (s.SLACATEGORYCHAR =:slaCategorychar1 or s.SLACATEGORYCHAR =:slaCategorychar2)";
                cmdParms.Add(new OracleParameter("slaCategorychar1", OracleDbType.Varchar2, slaCategorychar1, ParameterDirection.Input));
                cmdParms.Add(new OracleParameter("slaCategorychar2", OracleDbType.Varchar2, slaCategorychar2, ParameterDirection.Input));
            }
            if (site != null)
            {
                sqlStr += " and c.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and c.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and c.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and c.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s.slastatus";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //totest
        //CHG:Infrastructure Change工单详细信息 
        //需要传入slaCategorychar,priority,slaStatus
        //(slaStatus=1 满足 Inprocess/Met,2 不满足Missed/Missed Coal,3警告Warning,4等待Pending)
        public List<SLAInfoModel> SLAInfoToChange(int? site, int? bnid, DateTime? sTime, DateTime? eTime, string slaCategorychar1, string slaCategorychar2, int priority, int slaStatus)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select c.request_id ID,s.slatitle Summary,c.priority Priority,c.status Status from change c inner join sla s on c.request_id = s.applicationid where 1=1";
            sqlStr += " and c.priority =:priority";
            cmdParms.Add(new OracleParameter("priority", OracleDbType.Int32, priority, ParameterDirection.Input));
            //slaStatus判断
            if (slaStatus == 1)
            {
                sqlStr += " and (s.slaStatus =1 or s.slaStatus =3)";
            }
            if (slaStatus == 2)
            {
                sqlStr += " and (s.slaStatus =4 or s.slaStatus =5)";
            }
            if (slaStatus == 3)
            {
                sqlStr += " and s.slaStatus =6";
            }
            if (slaStatus == 4)
            {
                sqlStr += " and s.slaStatus =2";
            }
            if (slaCategorychar1 != null && slaCategorychar2 != null)
            {
                sqlStr += " and (s.SLACATEGORYCHAR =:slaCategorychar1 or s.SLACATEGORYCHAR =:slaCategorychar2)";
                cmdParms.Add(new OracleParameter("slaCategorychar1", OracleDbType.Varchar2, slaCategorychar1, ParameterDirection.Input));
                cmdParms.Add(new OracleParameter("slaCategorychar2", OracleDbType.Varchar2, slaCategorychar2, ParameterDirection.Input));
            }
            if (site != null)
            {
                sqlStr += " and c.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and c.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and c.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and c.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SLAInfoModel>.GetModel_By_OracleDataReader(dr);
        }

        //totest
        //Change供应商情况
        public List<SiteBranchAndWorkListModel> SLAChangeToFirm(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select t1.alls,t1.sign,t2.sign2,t3.sign3 from";
            sqlStr += "(select Count(*) sign,c.firm alls from Change c inner join sla s on c.REQUEST_ID = s.applicationid where s.applicationtype = 'CHG:Infrastructure Change' and c.firm is not null and (s.SLACATEGORYCHAR = '请求解决时间' Or s.SLACATEGORYCHAR = '请求响应时间')";
            if (site != null)
            {
                sqlStr += " and c.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and c.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and c.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and c.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by c.firm) t1 left join";
            sqlStr += "(select Count(*) sign2,c.firm alls from Change c inner join sla s on c.REQUEST_ID = s.applicationid where s.applicationtype = 'CHG:Infrastructure Change' and c.firm is not null and c.FirmProStatus = 1 and (s.SLACATEGORYCHAR = '请求解决时间' Or s.SLACATEGORYCHAR = '请求响应时间')";
            if (site != null)
            {
                sqlStr += " and c.siteid=:siteid1";
                cmdParms.Add(new OracleParameter("siteid1", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and c.BusinessSystemId = :bnid1";
                cmdParms.Add(new OracleParameter("bnid1", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and c.TicketCreateTime >= :sTime1";
                cmdParms.Add(new OracleParameter("sTime1", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and c.TicketCreateTime <= :eTime1";
                cmdParms.Add(new OracleParameter("eTime1", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by c.firm) t2 on t1.alls = t2.alls left join";
            sqlStr += "(select Count(*) sign3,c.firm alls from Change c inner join sla s on c.REQUEST_ID = s.applicationid where s.applicationtype = 'CHG:Infrastructure Change' and c.firm is not null and c.FirmProStatus = 2 and (s.SLACATEGORYCHAR = '请求解决时间' Or s.SLACATEGORYCHAR = '请求响应时间')";
            if (site != null)
            {
                sqlStr += " and c.siteid=:siteid2";
                cmdParms.Add(new OracleParameter("siteid2", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and c.BusinessSystemId = :bnid2";
                cmdParms.Add(new OracleParameter("bnid2", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and c.TicketCreateTime >= :sTime2";
                cmdParms.Add(new OracleParameter("sTime2", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and c.TicketCreateTime <= :eTime2";
                cmdParms.Add(new OracleParameter("eTime2", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by c.firm) t3 on t1.alls = t3.alls";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchAndWorkListModel>.GetModel_By_OracleDataReader(dr);
        }

        //totest
        //PBM:Problem Investigation响应/解决率
        //需要传入slaCategorychar,impact
        public List<IncidentAndChangeModel> SLAProblem(int? site, int? bnid, DateTime? sTime, DateTime? eTime, string slaCategorychar1, string slaCategorychar2, int priority)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,s.slastatus ID from sla s inner join Problem p on s.applicationid = p.request_id where s.applicationtype = 'PBM:Problem Investigation' and (s.slastatus = 3 or s.slastatus = 4 or s.slastatus = 5) and p.Priority = :priority";
            cmdParms.Add(new OracleParameter("priority", OracleDbType.Int32, priority, ParameterDirection.Input));
            if (slaCategorychar1 != null && slaCategorychar2 != null)
            {
                sqlStr += " and (s.SLACATEGORYCHAR =:slaCategorychar1 or s.SLACATEGORYCHAR =:slaCategorychar2)";
                cmdParms.Add(new OracleParameter("slaCategorychar1", OracleDbType.Varchar2, slaCategorychar1, ParameterDirection.Input));
                cmdParms.Add(new OracleParameter("slaCategorychar2", OracleDbType.Varchar2, slaCategorychar2, ParameterDirection.Input));
            }
            if (site != null)
            {
                sqlStr += " and p.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and p.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and p.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and p.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s.slastatus";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //totest
        //PBM:Problem Investigation工单详细信息 
        //需要传入slaCategorychar,impact,slaStatus
        public List<SLAInfoModel> SLAInfoToProblem(int? site, int? bnid, DateTime? sTime, DateTime? eTime, string slaCategorychar1, string slaCategorychar2, int priority, int slaStatus)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select p.request_id ID,s.slatitle Summary,p.priority Priority,p.status Status from problem p inner join sla s on p.request_id = s.applicationid where 1=1";
            sqlStr += " and p.priority =:priority";
            cmdParms.Add(new OracleParameter("priority", OracleDbType.Int32, priority, ParameterDirection.Input));
            //slaStatus判断
            if (slaStatus == 1)
            {
                sqlStr += " and (s.slaStatus =1 or s.slaStatus =3)";
            }
            if (slaStatus == 2)
            {
                sqlStr += " and (s.slaStatus =4 or s.slaStatus =5)";
            }
            if (slaStatus == 3)
            {
                sqlStr += " and s.slaStatus =6";
            }
            if (slaStatus == 4)
            {
                sqlStr += " and s.slaStatus =2";
            }
            if (slaCategorychar1 != null && slaCategorychar2 != null)
            {
                sqlStr += " and (s.SLACATEGORYCHAR =:slaCategorychar1 or s.SLACATEGORYCHAR =:slaCategorychar2)";
                cmdParms.Add(new OracleParameter("slaCategorychar1", OracleDbType.Varchar2, slaCategorychar1, ParameterDirection.Input));
                cmdParms.Add(new OracleParameter("slaCategorychar2", OracleDbType.Varchar2, slaCategorychar2, ParameterDirection.Input));
            }
            if (site != null)
            {
                sqlStr += " and p.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and p.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and p.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and p.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SLAInfoModel>.GetModel_By_OracleDataReader(dr);
        }

        //totest
        //Problem供应商情况
        public List<SiteBranchAndWorkListModel> SLAProblemToFirm(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select t1.alls,t1.sign,t2.sign2,t3.sign3 from";
            sqlStr += "(select Count(*) sign,p.firm alls from problem p inner join sla s on p.REQUEST_ID = s.applicationid where s.applicationtype = 'PBM:Problem Investigation' and p.firm is not null and (s.SLACATEGORYCHAR = '请求解决时间' Or s.SLACATEGORYCHAR = '请求响应时间')";
            if (site != null)
            {
                sqlStr += " and p.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and p.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and p.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and p.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by p.firm) t1 left join";
            sqlStr += "(select Count(*) sign2,p.firm alls from problem p inner join sla s on p.REQUEST_ID = s.applicationid where s.applicationtype = 'PBM:Problem Investigation' and p.firm is not null and p.FirmProStatus = 2000 and (s.SLACATEGORYCHAR = '请求解决时间' Or s.SLACATEGORYCHAR = '请求响应时间')";
            if (site != null)
            {
                sqlStr += " and p.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and p.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and p.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and p.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by p.firm) t2 on t1.alls = t2.alls left join";
            sqlStr += "(select Count(*) sign3,p.firm alls from problem p inner join sla s on p.REQUEST_ID = s.applicationid where s.applicationtype = 'PBM:Problem Investigation' and p.firm is not null and p.FirmProStatus = 3000 and (s.SLACATEGORYCHAR = '请求解决时间' Or s.SLACATEGORYCHAR = '请求响应时间')";
            if (site != null)
            {
                sqlStr += " and p.siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and p.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and p.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and p.TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by p.firm) t3 on t1.alls = t3.alls";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchAndWorkListModel>.GetModel_By_OracleDataReader(dr);
        }


        public decimal UserID
        {
            get
            {
                return this._userID;
            }
            set
            {
                this._userID = value;
            }
        }


        public List<SiteAndSLASumModel> GetSiteAndSLA()
        {
          return   ModelHelper<SiteAndSLASumModel>.GetModel_By_DirectSql(sqlGetSiteAndSLA, new List<OracleParameter>()
                {
                    new OracleParameter("userID", OracleDbType.Decimal, _userID, ParameterDirection.Input)
                }, oda);
        }


        public List<SiteAndSLADetailModel> GetSiteAndSLA_BySystemName(decimal userID, string systemName)
        {
            return ModelHelper<SiteAndSLADetailModel>.GetModel_By_DirectSql(sqlGetSiteAndSLA_BySystemname, new List<OracleParameter>()
                {
                    new OracleParameter("SystemName1", OracleDbType.NVarchar2, systemName, ParameterDirection.Input),
                    new OracleParameter("SystemName2", OracleDbType.NVarchar2, systemName, ParameterDirection.Input),
                    new OracleParameter("SystemName3", OracleDbType.NVarchar2, systemName, ParameterDirection.Input),
                    new OracleParameter("userID", OracleDbType.Decimal, userID, ParameterDirection.Input)
                }, oda);
        }
    }
}
